﻿<!--#include file="conn.asp"#-->
<!--#include file="sessao.asp"#-->
<!--#include file="getsessao.asp"#-->
<!--#include file="fn_multiselect.asp"#-->
<!--#include file="fn_combo.asp"#-->
<%
Select Case request("acao")
	Case "getRegional"
		getRegional()
	Case "getRegionalUF"
		getRegionalUF()
	Case "getUF"
		getUF()
	Case "getDDD"
		getDDD()
	Case "getRegionalUFDDD"
		getRegionalUFDDD()
	Case "getUsuario"
		getUsuario()
	Case "getUsuarioInformacao"
		getUsuarioInformacao()
	Case "getPerfil"
		getPerfil()
	Case "getStatus"
		getStatus()
	Case "validarMatricula"
		validarMatricula()
	Case "gravar"
		gravar()
End Select

Sub gravar()
	Dim db_string
	Dim cod_usuario
	Dim rsu
	Dim dt
	writeHeader("")
	if request("cod_usuario") = "0" or request("cod_usuario") = "" then
		db_string = "set nocount on;"
		db_string = db_string & "insert into informacao.tb_usuarios ("
		db_string = db_string & "cod_perfil,"
		db_string = db_string & "cod_status_usuario,"
		db_string = db_string & "matricula,"
		db_string = db_string & "cpf,"
		db_string = db_string & "gsm,"
		db_string = db_string & "nome,"
		db_string = db_string & "email,"
		db_string = db_string & "dt_nascimento,"
		db_string = db_string & "dt_cadastro,"
		db_string = db_string & "dt_alteracao,"
		db_string = db_string & "acesso_sistema,"
		db_string = db_string & "cod_usuario_tipo "
		db_string = db_string & ") values ("
		db_string = db_string & request("cmb_cod_perfil")&","
		db_string = db_string & request("cmb_cod_status")&","
		db_string = db_string & "'"&request("matricula_cadastro")&"',"
		db_string = db_string & "'"&replace(replace(request("cpf_cadastro"),".",""),"-","")&"',"
		db_string = db_string & "'"&replace(replace(replace(replace(request("gsm_cadastro"),"(",""),")","")," ",""),"-","")&"',"
		db_string = db_string & "'"&request("nome_cadastro")&"',"
		db_string = db_string & "'"&lcase(request("email_cadastro")&"")&"',"
		if request("dt_nascimento_cadastro") <> "" then
			dt = split(request("dt_nascimento_cadastro"),"/")
			db_string = db_string & "'"&dt(2)&dt(1)&dt(0)&"',"
		else
			db_string = db_string & "null,"
		end if
		db_string = db_string & "getdate(),"
		db_string = db_string & "getdate(),"
		db_string = db_string & request("acesso_sistema_cadastro")&","
		db_string = db_string & "2"
		db_string = db_string & ");"
		db_string = db_string & " select SCOPE_IDENTITY() as id ;"
	else
		db_string = "set nocount on;"
		if request("cod_tipo") = "1" then
			db_string = db_string & "update informacao.tb_usuarios set "
			db_string = db_string & "dt_alteracao=getdate(),"
			db_string = db_string & "email='"&lcase(request("email_cadastro")&"")&"',"
			db_string = db_string & "gsm='"&replace(replace(replace(replace(request("gsm_cadastro"),"(",""),")","")," ",""),"-","")&"',"
			db_string = db_string & "nome='"&request("nome_cadastro")&"' "
			db_string = db_string & " where cod_usuario="&request("cod_usuario")&";"
		else
			db_string = db_string & "update informacao.tb_usuarios set "
			db_string = db_string & "cod_perfil="&request("cmb_cod_perfil")&","
			db_string = db_string & "cod_status_usuario="&request("cmb_cod_status")&","
			db_string = db_string & "matricula='"&request("matricula_cadastro")&"',"
			db_string = db_string & "cpf='"&replace(replace(request("cpf_cadastro"),".",""),"-","")&"',"
			db_string = db_string & "gsm='"&replace(replace(replace(request("gsm_cadastro"),"(",""),")","")," ","")&"',"
			db_string = db_string & "nome='"&request("nome_cadastro")&"',"
			db_string = db_string & "email='"&lcase(request("email_cadastro")&"")&"',"
			if request("dt_nascimento_cadastro") <> "" then
				dt = split(request("dt_nascimento_cadastro"),"/")
				db_string = db_string & "dt_nascimento='"&dt(2)&dt(1)&dt(0)&"',"
			else
				db_string = db_string & "dt_nascimento=null,"
			end if
			db_string = db_string & "acesso_sistema="&request("acesso_sistema_cadastro")&","
			db_string = db_string & "dt_alteracao=getdate() "
			db_string = db_string & " where cod_usuario="&request("cod_usuario")&";"
		end if
		db_string = db_string & "select "&request("cod_usuario")&" as id;"
	end if
	set rsu = getRs(db_string)
	if not rsu.eof then
		cod_usuario = rsu("id")
	end if
	set rsu = nothing
	if request("cod_tipo") <> "1" and request("cod_tipo") <> "" then
		db_string = ""
		db_string = db_string & " delete informacao.tb_usuario_regional where cod_usuario="&cod_usuario&";"
		if request("chk_reg_cadastro_select") <> "" then
			db_string = db_string & " insert into informacao.tb_usuario_regional (cod_regional,cod_usuario)  ("
			db_string = db_string & " select cod_regional,"&cod_usuario&" from informacao.tb_regionais where cod_regional in("&request("chk_reg_cadastro_select")&")"
			db_string = db_string & " )"
		end if
		db_string = db_string & " delete informacao.tb_usuario_uf where cod_usuario="&cod_usuario&";"
		if request("chk_uf_cadastro_select") <> "" then
			db_string = db_string & " insert into informacao.tb_usuario_uf (cod_uf,cod_usuario)  ("
			db_string = db_string & " select cod_uf,"&cod_usuario&" from informacao.tb_ufs where cod_uf in("&request("chk_uf_cadastro_select")&")"
			db_string = db_string & " )"
		end if
		db_string = db_string & " delete informacao.tb_usuario_ddd where cod_usuario="&cod_usuario&";"
		if request("chk_ddd_cadastro_select") <> "" then
			db_string = db_string & " insert into informacao.tb_usuario_ddd (cod_ddd,cod_usuario)  ("
			db_string = db_string & " select cod_ddd,"&cod_usuario&" from informacao.tb_ddds where cod_ddd in("&request("chk_ddd_cadastro_select")&")"
			db_string = db_string & " )"
		end if
		if db_string <> "" then 
			executeQuery(db_string)
		end if
	end if
End sub


Sub validarMatricula()
	Dim db_string
	Dim rsu
	writeHeader("")
	db_string = "select matricula from informacao.tb_usuarios "
	db_string = db_string & " where 1=1 "
	if request("cod_usuario") <> "" and request("cod_usuario") <> "0" then
		db_string = db_string & " and not cod_usuario="&request("cod_usuario")
	end if
	db_string = db_string & " and matricula='"&request("matricula")&"' "
	set rsu = getRs(db_string)
	if not rsu.eof then
		response.write "false"
	else
		response.write "true"
	end if
	set rsu = nothing
	response.end
End Sub

Sub getRegional()
	Dim db_string 
	db_string = "select reg.cod_regional,reg.sigla_regional,case when isnull(rp.cod_usuario,0) <> 0 then 'checked' else '' end as checked from informacao.tb_regionais as reg"
	db_string = db_string & " left outer join informacao.tb_usuario_regional as rp "
	db_string = db_string & " on rp.cod_usuario="&request("cod_usuario")&" and rp.cod_regional=reg.cod_regional "
	db_string = db_string & " order by reg.sigla_regional "
	retorno  = fn_multiselect(db_string,request("nome"))
	response.write retorno
End Sub

Sub getRegionalUF()
	Dim db_string
	Dim cod_usuario
	if request("load") = "0" then
		cod_usuario = "0"
	else
		cod_usuario = request("cod_usuario")
	end if
	db_string = "select distinct uf.cod_uf,uf.uf,case when isnull(uff.cod_usuario,0) <> 0 then 'checked' else '' end as checked from informacao.tb_ufs as uf "
	db_string = db_string & " inner join informacao.tb_regional_uf as ruf on ruf.cod_uf=uf.cod_uf and ruf.cod_regional in("&request("ids")&") "
	db_string = db_string & " left outer join informacao.tb_usuario_uf as uff "
	db_string = db_string & " on uff.cod_usuario="&cod_usuario&" and uf.cod_uf=uff.cod_uf "
	db_string = db_string & " order by uf.uf "
	retorno  = fn_multiselect(db_string,request("nome"))
	response.write retorno
End Sub

Sub getUF()
	Dim db_string
	Dim cod_usuario
	if request("load") = "0" then
		cod_usuario = "0"
	else
		cod_usuario = request("cod_usuario")
	end if
	db_string = "select uf.cod_uf,uf.uf,case when isnull(uff.cod_usuario,0) <> 0 then 'checked' else '' end as checked from informacao.tb_ufs as uf "
	db_string = db_string & " left outer join informacao.tb_usuario_uf as uff "
	db_string = db_string & " on uff.cod_usuario="&cod_usuario&" and uf.cod_uf=uff.cod_uf "
	db_string = db_string & " order by uf.uf "
	retorno  = fn_multiselect(db_string,request("nome"))
	response.write retorno
End Sub


Sub getDDD()
	Dim db_string
	Dim cod_usuario
	if request("load") = "0" then
		cod_usuario = "0"
	else
		cod_usuario = request("cod_usuario")
	end if
	db_string = "select ddd.cod_ddd,ddd.ddd,case when isnull(dddu.cod_usuario,0) <> 0 then 'checked' else '' end as checked from informacao.tb_ddds as ddd "
	db_string = db_string & " left outer join informacao.tb_usuario_ddd as dddu "
	db_string = db_string & " on dddu.cod_usuario="&cod_usuario&" and dddu.cod_ddd=ddd.cod_ddd "
	db_string = db_string & " order by ddd.ddd "
	retorno  = fn_multiselect(db_string,request("nome"))
	response.write retorno
End Sub


Sub getRegionalUFDDD()
	Dim db_string
	Dim cod_usuario
	if request("load") = "0" then
		cod_usuario = "0"
	else
		cod_usuario = request("cod_usuario")
	end if
	db_string = "select distinct ddd.cod_ddd,ddd.ddd,case when isnull(dddu.cod_usuario,0) <> 0 then 'checked' else '' end as checked from informacao.tb_ddds as ddd "
	db_string = db_string & " inner join informacao.tb_regional_uf_ddd as ruddd on ddd.cod_ddd=ruddd.cod_ddd "
	if request("idregs") <> "" then
		db_string = db_string & " and ruddd.cod_regional in("&request("idregs")&")"
	end if
	if request("idufs") <> "" then
		db_string = db_string & " and ruddd.cod_uf in("&request("idufs")&")"
	end if
	db_string = db_string & " left outer join informacao.tb_usuario_ddd as dddu "
	db_string = db_string & " on dddu.cod_usuario="&cod_usuario&" and dddu.cod_ddd=ddd.cod_ddd "
	db_string = db_string & " order by ddd.ddd "
	retorno  = fn_multiselect(db_string,request("nome"))
	response.write retorno
End Sub

Sub getUsuario()
	

	Dim db_string

	Dim rsUsuario
	Dim Rsall

	Dim vregional
	Dim vuf
	Dim vddd
	
	Dim indice

	writeHeader("")

	if request("cod_usuario") <> "" then

		db_string = "select r.sigla_regional "
		db_string = db_string & " from informacao.tb_regionais as r "
		db_string = db_string & " inner join informacao.tb_usuario_regional as ur on ur.cod_regional=r.cod_regional "
		db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")

		set Rsall = getRs(db_string)
		vregional = ""
		indice = 1
		while not Rsall.eof
			if indice > 1 then
				vregional = vregional & ","
			end if
			vregional = vregional & Rsall("sigla_regional")
			indice = indice + 1
			Rsall.movenext
		wend
		Rsall.close

		db_string = "select r.uf "
		db_string = db_string & " from informacao.tb_ufs as r "
		db_string = db_string & " inner join informacao.tb_usuario_uf as ur on ur.cod_uf=r.cod_uf "
		db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")
		set Rsall = getRs(db_string)
		vuf = ""
		indice = 1
		while not Rsall.eof
			if indice > 1 then
				vuf = vuf & ","
			end if
			vuf = vuf & Rsall("uf")
			indice = indice + 1
			Rsall.movenext
		wend
		Rsall.close

		db_string = "select r.ddd "
		db_string = db_string & " from informacao.tb_ddds as r "
		db_string = db_string & " inner join informacao.tb_usuario_ddd as ur on ur.cod_ddd=r.cod_ddd "
		db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")
		set Rsall = getRs(db_string)
		vddd = ""
		indice = 1
		while not Rsall.eof
			if indice > 1 then
				vddd = vddd & ","
			end if
			vddd = vddd & Rsall("ddd")
			indice = indice + 1
			Rsall.movenext
		wend
		Rsall.close	
		set Rsall = nothing



		db_string = " select		u.matricula,"
		db_string = db_string & "   case when isnull(u.cpf,'') <> '' then "
		db_string = db_string & " 		left(u.cpf,3)+'.'+right(left(u.cpf,6),3)+'.'+left(right(u.cpf,5),3)+'-'+right(u.cpf,2) "
		db_string = db_string & "   else "
		db_string = db_string & "		'' "
		db_string = db_string & "   end "
		db_string = db_string & "	as cpf,"
		db_string = db_string & "	u.nome, "
		db_string = db_string & "	u.email, "
		db_string = db_string & " 	case when u.acesso_sistema = 1 then 'Sim' else 'Não' end as acesso,"
		db_string = db_string & "	case when u.acesso_sistema = 1 then '1' else '0' end as acesso_sistema,"
		db_string = db_string & "   case when isnull(u.gsm,'') <> '' then"
		db_string = db_string & "   	case when len(u.gsm) < 11 then "
		db_string = db_string & "			'('+left(u.gsm,2)+') '+right(left(u.gsm,len(u.gsm)-4),len(u.gsm)-6)+'-'+right(u.gsm,4) "
		db_string = db_string & " 		else"
		db_string = db_string & "			'('+left(u.gsm,2)+') '+right(left(u.gsm,len(u.gsm)-4),len(u.gsm)-6)+right(u.gsm,4) "
		db_string = db_string & "		end "
		db_string = db_string & "   else "
		db_string = db_string & "		'' "
		db_string = db_string & "   end "
		db_string = db_string & "		as gsm,"
		db_string = db_string & "	u.dt_nascimento,"
		db_string = db_string & "	u.dt_cadastro,"
		db_string = db_string & "	u.dt_alteracao,"
		db_string = db_string & "	u.cod_perfil, "
		db_string = db_string & "	a.desc_area+' / '+c.desc_cargo as desc_perfil, "
		db_string = db_string & "	u.cod_status_usuario as cod_status,"
		db_string = db_string & "   st.desc_status,"
		db_string = db_string & "	u.cod_usuario_tipo as cod_tipo,"
		db_string = db_string & "	t.desc_tipo as tipo "
		db_string = db_string & " from informacao.tb_usuarios as u "
		db_string = db_string & " inner join informacao.tb_status_usuario as st on st.cod_status_usuario=u.cod_status_usuario "
		db_string = db_string & " inner join informacao.tb_usuario_tipo as t on t.cod_usuario_tipo=u.cod_usuario_tipo "
		db_string = db_string & " inner join informacao.tb_perfis as p on p.cod_perfil=u.cod_perfil "
		db_string = db_string & " inner join informacao.tb_areas as a on a.cod_area=p.cod_area "
		db_string = db_string & " inner join informacao.tb_cargos as c on c.cod_cargo=p.cod_cargo "

		db_string = db_string & " where u.cod_usuario="&request("cod_usuario")
		set rsUsuario = getRs(db_string)
		if not rsUsuario.eof then
			
			retorno = "{success : true, data: {"
			retorno = retorno & "'matricula':'"&rsUsuario("matricula")&"',"
			retorno = retorno & "'cpf':'"&rsUsuario("cpf")&"',"
			retorno = retorno & "'nome':'"&rsUsuario("nome")&"',"
			retorno = retorno & "'email':'"&rsUsuario("email")&"',"
			retorno = retorno & "'gsm':'"&rsUsuario("gsm")&"',"
			retorno = retorno & "'dt_nascimento':'"&rsUsuario("dt_nascimento")&"',"
			retorno = retorno & "'dt_cadastro':'"&rsUsuario("dt_cadastro")&"',"
			retorno = retorno & "'dt_alteracao':'"&rsUsuario("dt_alteracao")&"',"

			retorno = retorno & "'inf_cadastro_regional':'"&vregional&"',"
			retorno = retorno & "'inf_cadastro_uf':'"&vuf&"',"
			retorno = retorno & "'inf_cadastro_ddd':'"&vddd&"',"
			retorno = retorno & "'inf_cadastro_acesso':'"&rsUsuario("acesso")&"',"

			retorno = retorno & "'acesso_sistema_cadastro':'"&rsUsuario("acesso_sistema")&"',"

			retorno = retorno & "'cod_tipo':'"&rsUsuario("cod_tipo")&"',"
			retorno = retorno & "'tipo':'"&rsUsuario("tipo")&"',"

			retorno = retorno & "'cod_perfil':'"&rsUsuario("cod_perfil")&"',"
			retorno = retorno & "'desc_perfil':'"&rsUsuario("desc_perfil")&"',"

			retorno = retorno & "'cod_status':'"&rsUsuario("cod_status")&"', "
			retorno = retorno & "'desc_status':'"&rsUsuario("desc_status")&"' }"
			retorno = retorno & "}"
		End If
		rsUsuario.close
	End If
	set rsUsuario = nothing
	response.write retorno
	response.end
End Sub

Sub getStatus()
	Dim db_string
	db_string = " select s.cod_status_usuario,s.desc_status as desc_perfil,case when isnull(u.cod_status_usuario,0) <> 0 then 1 else 0 end as selected from informacao.tb_status_usuario as s "
	db_string = db_string & " left outer join informacao.tb_usuarios as u "
	db_string = db_string & " on u.cod_usuario="&request("cod_usuario")&" and u.cod_status_usuario=s.cod_status_usuario "
	db_string = db_string & " order by desc_status "
	retorno  = fn_combo(db_string,true,"")
	response.write retorno
End sub

Sub getPerfil()
	Dim db_string
	db_string = " select p.cod_perfil,a.desc_area+' / '+c.desc_cargo as desc_perfil,case when isnull(u.cod_perfil,0) <> 0 then 1 else 0 end as selected from informacao.tb_perfis as p "
	db_string = db_string & " inner join informacao.tb_areas as a on a.cod_area=p.cod_area "
	db_string = db_string & " inner join informacao.tb_cargos as c on c.cod_cargo=p.cod_cargo "
	db_string = db_string & " left outer join informacao.tb_usuarios as u "
	db_string = db_string & " on u.cod_usuario="&request("cod_usuario")&" and u.cod_perfil=p.cod_perfil "
	db_string = db_string & " where a.cod_status_area=1 and c.cod_status_cargo=1 and p.cod_status_perfil=1 and p.cod_usuario_tipo<>1 "
	db_string = db_string & " order by desc_perfil "
	retorno  = fn_combo(db_string,true,"")
	response.write retorno
End sub


Sub getUsuarioInformacao()
	Dim db_string
	Dim Rsu
	Dim Rsall
	Dim retorno
	Dim vregional
	Dim vuf
	Dim vddd
	Dim indice
	writeHeader("")

	db_string = "select r.sigla_regional "
	db_string = db_string & " from informacao.tb_regionais as r "
	db_string = db_string & " inner join informacao.tb_usuario_regional as ur on ur.cod_regional=r.cod_regional "
	db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")

	set Rsall = getRs(db_string)
	vregional = ""
	indice = 1
	while not Rsall.eof
		if indice > 1 then
			vregional = vregional & ", "
		end if
		vregional = vregional & Rsall("sigla_regional")
		indice = indice + 1
		Rsall.movenext
	wend
	Rsall.close

	db_string = "select r.uf "
	db_string = db_string & " from informacao.tb_ufs as r "
	db_string = db_string & " inner join informacao.tb_usuario_uf as ur on ur.cod_uf=r.cod_uf "
	db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")
	set Rsall = getRs(db_string)
	vuf = ""
	indice = 1
	while not Rsall.eof
		if indice > 1 then
			vuf = vuf & ", "
		end if
		vuf = vuf & Rsall("uf")
		indice = indice + 1
		Rsall.movenext
	wend
	Rsall.close

	db_string = "select r.ddd "
	db_string = db_string & " from informacao.tb_ddds as r "
	db_string = db_string & " inner join informacao.tb_usuario_ddd as ur on ur.cod_ddd=r.cod_ddd "
	db_string = db_string & " where ur.cod_usuario="&request("cod_usuario")
	set Rsall = getRs(db_string)
	vddd = ""
	indice = 1
	while not Rsall.eof
		if indice > 1 then
			vddd = vddd & ", "
		end if
		vddd = vddd & Rsall("ddd")
		indice = indice + 1
		Rsall.movenext
	wend
	Rsall.close	
	set Rsall = nothing

	db_string = "select	u.matricula,"
	db_string = db_string & "	u.cpf, "
	db_string = db_string & "	u.nome, "
	db_string = db_string & "	u.email, "
	db_string = db_string & "	u.dt_nascimento, "
	db_string = db_string & "	case when u.gsm <> '' then "
	db_string = db_string & "		'('+left(u.gsm,2)+') '+RIGHT(u.gsm,LEN(u.gsm)-2)  "
	db_string = db_string & "	else "
	db_string = db_string & "		'' "
	db_string = db_string & "	end as gsm, "
	db_string = db_string & "	case when u.acesso_sistema = 1 then "
	db_string = db_string & "		'Sim' "
	db_string = db_string & "	else "
	db_string = db_string & "		'Não' "
	db_string = db_string & "	end as acesso, " 
	db_string = db_string & "	ut.desc_tipo as tipo, "
	db_string = db_string & "	u.dt_cadastro, "
	db_string = db_string & "	u.dt_alteracao, "
	db_string = db_string & "	a.desc_area as area, "
	db_string = db_string & "	c.desc_cargo as cargo "
	db_string = db_string & " from informacao.tb_usuarios as u " 
	db_string = db_string & " inner join informacao.tb_usuario_tipo as ut on ut.cod_usuario_tipo=u.cod_usuario_tipo "
	db_string = db_string & " inner join informacao.tb_perfis as p on p.cod_perfil=u.cod_perfil "
	db_string = db_string & " inner join informacao.tb_areas as a on a.cod_area=p.cod_area "
	db_string = db_string & " inner join informacao.tb_cargos as c on c.cod_cargo=p.cod_cargo "
	db_string = db_string & " where u.cod_usuario="&request("cod_usuario")
	set Rsu = getRs(db_string)
	if not Rsu.eof then
		retorno = "{success : true, data: {"
		retorno = retorno & "'inf_matricula':'"&rsu("matricula")&"',"
		retorno = retorno & "'inf_nome':'"&rsu("nome")&"',"
		retorno = retorno & "'inf_email':'"&rsu("email")&"',"
		retorno = retorno & "'inf_dt_alteracao':'"&rsu("dt_alteracao")&"',"
		retorno = retorno & "'inf_dt_cadastro':'"&rsu("dt_cadastro")&"',"
		retorno = retorno & "'inf_area':'"&rsu("area")&"',"
		retorno = retorno & "'inf_cargo':'"&rsu("cargo")&"',"
		retorno = retorno & "'inf_tipo':'"&rsu("tipo")&"',"
		retorno = retorno & "'inf_regional':'"&vregional&"',"
		retorno = retorno & "'inf_uf':'"&vuf&"',"
		retorno = retorno & "'inf_ddd':'"&vddd&"'}"
		retorno = retorno & "}"
	end if
	rsu.close
	set rsu = nothing
	response.write retorno
end Sub

%>